﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Reflection;
using System.Collections;
using System.Windows.Forms;
using System.Data.OleDb;
namespace OisLibrary
{
    public class ExcelOpt
    {
        #region 表数据写入Excel
        /// <summary>
        /// 表数据写入Excel
        /// </summary>
        /// <param name="SourceDataTable"></param>
        public static void TableToExcel(object[,] dataArray, string ExcelName ,int width)
        {
            

            object miss = Missing.Value;
            Excel.Application excelApp = new Excel.Application();                                           //开打Excel
            excelApp.Workbooks.Add(miss);

            try
            {
                Excel.Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[1];                        //创建工作表单

                workSheet.Cells.Select();                                                                   //全表自动列宽
                workSheet.Cells.Columns.AutoFit();

                Excel.Range myrange = workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[dataArray.Length / width, width]);      //取数组所在位置
                myrange.NumberFormat = "@";                                                                                  //设置文本格式
                Excel.Workbook workBook = excelApp.Workbooks[1];
                workSheet.get_Range(workSheet.Cells[1, 1], workSheet.Cells[dataArray.Length / width, width]).Value2 = dataArray;//把数组添加到工作表单上
                workBook.SaveAs(ExcelName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlExclusive, miss, miss, miss, miss,miss);     //保存文件
                workBook.Close(false, miss, miss);
                workBook = null;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                excelApp.Quit();
                excelApp = null;
                GC.Collect();
                //KillExcelProcess();//结束excel进程       
            }
        }


        public static DataTable getTableFromExcel(string excelFile)
        {
            string excelConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";Extended Properties=Excel 8.0";
            string excelCmdstr = string.Format("select * from [Sheet1$]");
            try
            {
                OleDbConnection oleDbConnection = new OleDbConnection(excelConnect);
                oleDbConnection.Open();

                OleDbCommand excelCmd = new OleDbCommand(excelCmdstr, oleDbConnection);
                OleDbDataAdapter excelDa = new OleDbDataAdapter(excelCmd);

                DataTable dt = new DataTable();
                excelDa.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                return null;
            }
        }


        #endregion
    }
}
